其實這東西在基礎那邊有寫過，https://ithelp.ithome.com.tw/articles/10402366 ，不過當時只是提一點他的原理，還有如何維護，以及維護時候發生的鎖。
這邊會開始說，統計資料實際上到底是如何影響效能的
在這裡的時候，https://ithelp.ithome.com.tw/articles/10404267 ，有提過最佳化程序，就是以成本為基礎在運作的。
而成本怎麼計算就來自於統計資料的數據。
在 SELECT 子句中的欄位不需要統計資料。真正需要統計資料的是用於篩選的欄位，WHERE、HAVING、JOIN。
掌握資料本身，以及 Predicate 中所參照欄位的資料分佈統計資料，是最佳化器決定如何選擇最佳策略來滿足查詢需求的主要驅動因素之一。
統計資料讓最佳化器能夠快速計算：某個欄位中某個指定值，大概會回傳多少筆資料列。有了資料列數量的估算值，最佳化器就能做出更好的選擇，找出更有效率的方式來擷取並處理你的資料。
建立索引的時候，SQL Server 會根據定義的 key，去自動建立統計資料，預設的狀況下，非索引欄位被用來 WHERE，SQL SERVER 也會自動建立統計資料。這是統計資料建立的時機。
一般來說預設都會自動讓統計資訊新一點。
不過我們仍然可以透過分析統計，來判斷統計資料是否有被妥善建立跟維護。很多時候其實還是要手動控制。
某個索引能在多大的程度上幫助我們查詢跑得更快，很大程度上取決於該索引 key 上的統計資料。
最佳化器會使用索引鍵欄位上的統計資料來估算資料列數量，而這些估算值會驅動許多其他決策。
SQL Server 有兩種儲存索引的方式：Rowstore 與 Columnstore，對於 Rowstore 索引 來說，統計資料會隨著索引自動建立。這個行為無法修改。
如果願意，也可以替 Columnstore 索引額外新增統計資料。新增到 Columnstore 索引上的 Nonclustered Index 仍然會有統計資料，因為它們本質上仍然是 Rowstore 索引。
資料變更可能會影響索引的選擇。
如果某個資料表中，某個欄位值只對應到一筆資料列，那麼使用索引可能會是非常好的選擇。但如果資料隨著時間改變，現在有大量資料列都符合該值，那麼這個索引就可能變得比較不實用。
這就是為什麼需要確保統計資料是最新的。
補充一點統計資訊更新的門檻，這在基礎那邊只是輕輕帶過而已
| 資料表類型 | 資料表中的資料列數量 | 更新門檻 |
|---|---|---|
| 暫存資料表 | 小於 6 筆 | 6 次變更 |
| 暫存資料表 | 介於 6 到 500 筆 | 500 次變更 |
| 永久資料表 | 小於 500 筆 | 500 次變更 |
| 兩者皆適用 | 大於 500 筆 | MIN(500 + (0.2 * n), SQRT(1,000 * n)) |
然後雖然可以用非同步的方式去更新統計資料，避免說在尖峰時段去更新統計資料，但是我強烈建議不要用這種方式處理。
我是從沒看過有這種系統因為自動統計資料維護而受到影響，絕大多數都可以從中受益。
做一個簡單測試去直觀的感受統計資料對效能的影響
--準備資料
DROP TABLE IF EXISTS dbo.Test1;
GO
CREATE TABLE dbo.Test1
(
C1 INT,
C2 INT IDENTITY
);
SELECT TOP 1500
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sC1,
master.dbo.syscolumns AS sC2;
INSERT INTO dbo.Test1
(
C1
)
SELECT n
FROM #Nums;
DROP TABLE #Nums;
CREATE NONCLUSTERED INDEX i1 ON dbo.Test1 (C1);
--去看執行計畫
SELECT t.C1,
t.C2
FROM dbo.Test1 AS t
WHERE t.C1 = 2;
--然後我要建立一個擴充事件，來觀察統計資料更新程序的行為，同時擷取查詢效能資訊。
CREATE EVENT SESSION [Statistics]
ON SERVER
ADD EVENT sqlserver.auto_stats
(ACTION
(
sqlserver.sql_text
)
WHERE (sqlserver.database_name = N'AdventureWorks2022')
),
ADD EVENT sqlserver.sql_batch_completed
(WHERE (sqlserver.database_name = N'AdventureWorks2022'));
GO
ALTER EVENT SESSION [Statistics] ON SERVER STATE = START;
--然後新增一筆資料
INSERT INTO dbo.Test1
(
C1
)
VALUES
(2);
--然後再跑一次查詢
SELECT t.C1,
t.C2
FROM dbo.Test1 AS t
WHERE t.C1 = 2;
這時候會看到執行計劃跟沒有新增一筆資料以前一模一樣
擴充事件也會捕捉到
但是並沒有捕捉到任何統計資料更新，因為他沒有跨過 500 的門檻
--接著再執行 新增 1500 筆 故意去觸發統計資料更新
SELECT TOP 1500
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sC1,
master.dbo.syscolumns AS sC2;
INSERT INTO dbo.Test1
(
C1
)
SELECT 2
FROM #Nums;
DROP TABLE #Nums;
--然後再跑一次查詢
SELECT t.C1,
t.C2
FROM dbo.Test1 AS t
WHERE t.C1 = 2;
就會發現執行計畫變了
邏輯很簡單，因為一開始只有五筆 C1 = 2 的，所以用一開始的執行計畫巢狀迴圈他只需要去跟索引要五次資料就好。
但後來新增 1500 筆 C1 = 2，如果繼續用原本的巢狀迴圈，那他就要去要 1500 次索引，他覺得這樣效率很低，所以改用資料表掃描。
擴充事件也捕捉到 update 統計資訊
然後一樣的作法只是我這次故意停用自動更新統計資料
剩下全部都一樣
ALTER DATABASE AdventureWorks2022
SET AUTO_UPDATE_STATISTICS OFF;
GO
這是有開統計資訊更新的執行計畫
這是關掉之後
很明顯看到沒有更新統計資訊的執行計畫錯得離譜估計2、實際1502，因為統計資訊錯誤，所以得到錯誤的執行計畫，導致整個查詢緩慢。
實際運做的系統中，很常看到篩選條件或是 JOIN 條件，並不是索引 KEY 的一部份。
這種情況發生的時候，最佳化器還是需要了解該欄位的 CARDINALITY 才能制定出好的執行計畫。
雖然某個欄位沒有參與索引，代表沒有索引可以協助擷取資料，但最佳化器仍然可以使用統計資料來建立更好的執行計畫。
這就是為什麼預設情況下，SQL Server 會針對用於篩選的欄位自動建立統計資料。
有一種情境下，你可能會考慮停用自動建立統計資料：當你正在執行一系列 ad hoc T-SQL queries，而這些查詢之後再也不會被執行。
ad hoc 查詢是什麼之後會提
在這種情境下，SQL Server 為欄位建立統計資料的成本，有可能高於它帶來的效益
不過，即使如此，仍然需要驗證效能究竟是受到正面還是負面影響。
對大多數系統而言，除非有非常明確的證據，證明統計資料的建立正在主動造成系統痛點，否則應該保持自動建立統計資料啟用。
為了觀察「不屬於索引一部分的欄位」上的統計資料有什麼好處，建立兩個測試資料表，且兩者的資料分佈差異很大。
兩個資料表都包含 10,001 筆資料列。
Test1 資料表中，只有一筆資料的 Test1_C2 欄位值等於 1，其餘 10,000 筆 的值都是 2。
Test2 資料表則是完全相反的資料分佈
DROP TABLE IF EXISTS dbo.Test1;
GO
CREATE TABLE dbo.Test1
(
Test1_C1 INT IDENTITY,
Test1_C2 INT
);
INSERT INTO dbo.Test1
(
Test1_C2
)
VALUES
(1);
SELECT TOP 10000
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sC1,
master.dbo.syscolumns AS sC2;
INSERT INTO dbo.Test1
(
Test1_C2
)
SELECT 2
FROM #Nums;
GO
CREATE CLUSTERED INDEX i1 ON dbo.Test1 (Test1_C1);
-- Create second table with 10001 rows,
-- but opposite data distribution
IF
(
SELECT OBJECT_ID('dbo.Test2')
) IS NOT NULL
DROP TABLE dbo.Test2;
GO
CREATE TABLE dbo.Test2
(
Test2_C1 INT IDENTITY,
Test2_C2 INT
);
INSERT INTO dbo.Test2
(
Test2_C2
)
VALUES
(2);
INSERT INTO dbo.Test2
(
Test2_C2
)
SELECT 1
FROM #Nums;
DROP TABLE #Nums;
GO
CREATE CLUSTERED INDEX i1 ON dbo.Test2 (Test2_C1);
--然後用這個去觀察執行計畫的變化
SELECT t1.Test1_C2,
t2.Test2_C2
FROM dbo.Test1 AS t1
JOIN dbo.Test2 AS t2
ON t1.Test1_C2 = t2.Test2_C2
WHERE t1.Test1_C2 = 2;
然後可以用這個語法去查統計資料
SELECT s.name,
s.auto_created,
s.user_created
FROM sys.stats AS s
WHERE object_id = OBJECT_ID('Test1');
il 是建立 Clustered Index 產生的統計資料
_WA….那個是 SQL Server 自動建立的欄位統計資料
這時候把查詢改成
SELECT t1.Test1_C2,
t2.Test2_C2
FROM dbo.Test1 AS t1
JOIN dbo.Test2 AS t2
ON t1.Test1_C2 = t2.Test2_C2
WHERE t1.Test1_C2 = 1;
會看到執行計畫就變了，雖然變動很小，變動的地方是 join 的 inner table 互換了
但由此可知最佳化器，因為自動建立在相關欄位上的統計資料的原因，反映了資料分部差異，所以去修改變更執行計畫。
ALTER DATABASE AdventureWorks2022 SET AUTO_CREATE_STATISTICS OFF;
一樣來測試沒有統計資訊的話會發生什麼事
DROP TABLE IF EXISTS dbo.Test1;
GO
CREATE TABLE dbo.Test1
(
Test1_C1 INT IDENTITY,
Test1_C2 INT
);
INSERT INTO dbo.Test1
(
Test1_C2
)
VALUES
(1);
SELECT TOP 10000
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sC1,
master.dbo.syscolumns AS sC2;
INSERT INTO dbo.Test1
(
Test1_C2
)
SELECT 2
FROM #Nums;
GO
CREATE CLUSTERED INDEX i1 ON dbo.Test1 (Test1_C1);
-- Create second table with 10001 rows,
-- but opposite data distribution
IF
(
SELECT OBJECT_ID('dbo.Test2')
) IS NOT NULL
DROP TABLE dbo.Test2;
GO
CREATE TABLE dbo.Test2
(
Test2_C1 INT IDENTITY,
Test2_C2 INT
);
INSERT INTO dbo.Test2
(
Test2_C2
)
VALUES
(2);
INSERT INTO dbo.Test2
(
Test2_C2
)
SELECT 1
FROM #Nums;
DROP TABLE #Nums;
GO
CREATE CLUSTERED INDEX i1 ON dbo.Test2 (Test2_C1);
--然後用這個去觀察執行計畫的變化
SELECT t1.Test1_C2,
t2.Test2_C2
FROM dbo.Test1 AS t1
JOIN dbo.Test2 AS t2
ON t1.Test1_C2 = t2.Test2_C2
WHERE t1.Test1_C2 = 2;
在沒有統計資料的查詢中，Reads 數量與執行時間都高出許多。
因為沒有統計資料，最佳化器只能用數學啟發式規則進行猜測，推估資料可能如何分佈。
這些猜測的準確度，當然不如實際擁有資料分佈資訊來輔助決策。
統計資料由三種資料組成
最常被拿來用的是 histogram，這個會顯示資料樣本，最多可以包含 200 個 steps，並且會統計每個 steps 中各數直出現的次數。
這些 steps 會根據相關資料產生，來源是從整體資料中隨機分佈取樣而來。
steps 就只是直方圖 X 軸 分成幾個階段而已
重建一個 TABLE
DROP TABLE IF EXISTS dbo.Test1;
GO
CREATE TABLE dbo.Test1
(
C1 INT,
C2 INT IDENTITY
);
INSERT INTO dbo.Test1
(
C1
)
VALUES
(1);
SELECT TOP 10000
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns sc1,
master.dbo.syscolumns sc2;
INSERT INTO dbo.Test1
(
C1
)
SELECT 2
FROM #Nums;
DROP TABLE #Nums;
CREATE NONCLUSTERED INDEX FirstIndex ON
dbo.Test1 (C1);
然後用這個來取得統計資料
DBCC SHOW_STATISTICS(Test1, FirstIndex);
這三個東西就是剛剛講的 header、density graph、histogram
接下來解釋一下這是什麼意思
包含統計資料本身資訊
Name、Updated 很直觀不用解釋
Rows 代表 :
在統計資料被建立或更新的當下，資料表中的資料列數量
Rows Sampled :
為了建立這組統計資料，SQL Server 取樣了多少資料列
Density :
索引的平均 key 長度。
其餘的後面講
當最佳化器建立執行計畫時，會分析那些用在 join、having、where 子句中的欄位統計資料。
具有高選擇性的篩選條件，會限制從資料表中取回的資料列數量，這有助於最佳化器降低查詢成本。
例如，具有 Unique Index 的欄位會有非常高的選擇性，因為它會把符合條件的資料列限制為 1 筆。
相反地，具有低選擇性的篩選條件，會從資料表中回傳較大的結果集。
低選擇性的篩選條件可能會讓 Nonclustered Index 變得沒有效率。
高選擇性 = 篩選完剩很少資料。通常很效率很好
低選擇性 = 篩選玩還有很多資料。不適合一般 Nonclustered Index Seek
原因是：如果結果集很大，透過 Nonclustered Index 找到資料後，再回到基底資料表取資料，通常會比直接掃描 Clustered Index 更沒效率。
高選擇性的搜尋方式 :
例如有一張 user 表 100萬筆資料
然後去下搜尋 where email = ‘abc@gmail.com’
這時候理論上來說他只會回傳一筆資料，這就叫高選擇性，相反就是低選擇
如果資料表是 Heap，則是直接掃描基底資料表。
統計資料會用 Density Ratio（密度比例） 的形式追蹤欄位的選擇性。
Density 有兩種計算方式：
基本公式如下：
Density = 1 / 欄位中的不同值數量
Density 永遠會是一個介於 0 到 1 之間的數字。
Density 越低，越適合作為索引鍵使用。
--計算 density 值
SELECT 1.0 / COUNT(DISTINCT C1)
FROM dbo.Test1;
因為C1 只有兩個值，所以這答案是 0.5，這不用算也知道
Density 會在 Histogram 無法使用時，用來估算資料列數量。
計算方式很簡單
估計資料列數 = Density * 資料表總列數
那最佳化器用 Density 去估計的方式就是用
假設有 100 萬筆資料，而 Density = 0.000001
代表每個值都很稀有
where id = 123 這種狀況下，就會判斷是高選擇性資料，進而讓最佳化器會使用 SEEK 的方式，去製作執行計畫。
相反的 如果 Density = 0.5
然後下
SELECT Name, Age
FROM Users
WHERE 性別 = ‘M’
這種狀況，一班來說回傳 50 萬筆，就會判斷低選擇，然後最佳化器就會更傾向使用 SCAN 去做執行計畫。
但又因為你有索引，所以SQL Server 是有可能會去 SCAN 索引，不是直接 SCAN 表，SCAN 索引就會造成多一次指向就是 lookup，就比直接 SCAN 表效率還來的低。
在這個案例中，那你就直接不要用索引把索引給砍了，效率還更高。
所以這才是常常會看到有人在說很相似的資料不要做非叢級索引的底層原因。
注意 : 這裡說的索引都是針對非叢級索引
還有我故意用 SELECT Name, Age，是想表達非叢級索引如果 key 建在性別上，又沒有 covering index 的話，要查 Name, Age 的話就會發生 lookup。
如果是 SELECT 性別，那就沒有 lookup 問題，那反而 scan 索引會比較快，因為這比整張 table 輕量。
但另外一種狀況又不一樣了
我的紅字有寫 Density 會在 Histogram 無法使用時，才用來估算資料列數量。
這個是因為，假如今天 TABLE 是 性別欄為，通常就是兩種資料男生女生
可是女生有 90 萬筆，男生 10 萬筆
這時候雖然 Density = 0.5，但是下 where 性別 = ‘M’ 的時候，因為回傳只會有 10萬筆，就有可能判斷成是高選擇，然後讓最佳化器去使用 SEEK 作執行計畫，所以才會有那個紅字，在 HISTOGRAM 無法使用時。
所以除了看 Density 這個值以外，也要看資料分布。
這是統計資訊最常使用的資料
它包含五個欄位，最多200列資料，也就是 200 個 steps
這是每一個 range 的最高值，也就是每一個 steps 的邊界
前一個 RANGE_HI_KEY 到這目前這個 RANGE_HI_KEY 之間的資料列數量
例如說
| RANGE_HI_KEY |
|---|
| 10 |
| 20 |
那 20 的那個 RANGE_ROWS 就會是 WHERE column > 10 AND column < 20 的資料列數量。
在統計資料建立或更新的當下，RANGE_HI_KEY 這個值的資料列數量。
這個 range 區間內有多少不同的值
在該 range 區間內，每一個可能的 key value 平均會對應幾筆資料。
計算公式
AVG_RANGE_ROWS = RANGE_ROWS / DISTINCT_RANGE_ROWS
根據我們的案例，因為只有兩個 STEPS 而且KEY 還連續，所以 SQL SERVER 再估計的時候可以很直觀的去看，EQ_ROWS
WHERE = 1 就直接估計1筆 = 2 就 10000 筆很簡單
但如果是複雜一點
DBCC SHOW_STATISTICS(
'Sales.SalesOrderDetail',
'IX_SalesOrderDetail_ProductID'
);
這時候要問ID 是 827 的時候，會估計幾筆?
就要去看大於 827 的下一個 STEPS 的 AVG_RANGE_ROWS = 36.6667 筆
但這時候就不一定準了，因為AVG_RANGE_ROWS 是 上一個 STEPS 到下一個 STEPS 中間每一個值”平均”有幾筆，所以在這裡估計就有可能會有落差。
前面已經知道，統計主要就是由，Histogram 跟 Density 組成，然後查詢最佳化器就用這些資訊，去計算出估計資料列數，阿這個是有一個專業術語的就叫做 Cardinality。
一般的計算方式在上面有說了就是直接去看 Histogram
但如果是多個欄位被 where 時候，這個估計會去考慮每個欄位的可能的選擇性
再基礎的那裏有說過這東西再 2014 年有改版，但那時候沒有講太深入的計算方式，其計算方式如下
Selectivity1 * Power(Selectivity2, 1/2) * Power(Selectivity3, 1/4)…
他加入一個概念 : 資料欄位彼此之間有關連
然後原版的是
Selectivity1 * Selectivity2 * Selectivity3…
SELECT *
FROM Users
WHERE Gender = 'M'
AND City = 'Taipei'
AND IsActive = 1;
舉例來說，上面這種多欄位的 where，最佳化器在估算之前，要先去猜
Gender = 'M' 會剩幾筆？
City = 'Taipei' 會剩幾筆？
IsActive = 1 會剩幾筆？
三個條件一起用，最後會剩幾筆？
這時候就會去使用 histogram 或是 denstity 去看
假設 users 有 100 萬筆
WHERE Gender = ‘M’ 如果回傳 50萬筆 那 selectivity = 0.5
WHERE City = ‘Taipei’ 如果回傳 10萬筆 那 selectivity = 0.1
WHERE IsActive = 1 如果回傳 90 萬筆 那 selectivity = 0.9
這時候，舊版的估算方式就會是這樣
0.50.10.9=0.045
100萬 * 0.045 = 45000筆
那這種算法，是直接去預設每一個條件都完全獨立，可是現實中常常並不是這樣
雖然我是不建議去下一個你邏輯上知道有這個 WHERE 就一定包含另外一個 WHERE 的語法，例如說
WHERE 國家 = 'Taiwan' AND 城市 = ‘Taipei’
你這個語法 Taipei 一定 100% 是台灣，根本就不用去下前面那個 國家 = 'Taiwan' ，如果這種很明顯的邏輯，你的 table 裡面還是會有例外，那要做的事是去看你的 check 在幹嘛，為什麼會允許這種邏輯錯誤的資料存近來。
那剛剛是雖然，接下來就有個但是，如果真的是這種情況一定得篩選，而且欄位又有關連性，那這時候 2014 版本以後的估計方式會對這種狀況友善一點。
他會先從最有選擇性的資料 ( selectivity 最小、篩選完後資料勝最少的 ) 開始算，也就是
0.1 * 0.5^1/2 * 0.9 ^1/4 = 0.0689
100萬 * 0.0689 = 68900 筆
從這裡就可以看出，估計出兩種不同的筆數，就很有可能給出兩種不同的執行計畫，就會有效能上的不同。
有一個例外， IDENTITY 欄位，他估算的方式不是前面說的那幾種，之後再說
STEPS 最多就 200 個，一般要找估值直接去 STEPS 找，但是如果今天是超過 STEPS 最大值，統計資訊又還沒有更新
那就會很麻煩了
這時候他就只能去猜，2014 以前通常會預估只有 1 筆，當然這在 ID 上沒問題，可如果今天是日期 ‘20260713’ 可能會有五萬筆，結果只預估 1 筆，那在執行計劃上就會有很大落差。
2014 以後 (含)，超出範圍的話，他會用統計資料裡的平均資料列數來預估
這也有一個缺點，如果資料分部不均例如男生有九百九十萬筆、女生只有十萬筆，這種時候就會造成平均值失準，然後拿到爛的執行計畫。
通常這種狀況不太會發生，因為統計資料都會自動更新，這只是小細節。
但有一種狀況是幾乎無法用 Histogram 去預估的，就是以下這種寫法
DECLARE @Gender char(1) = 'M';
SELECT *
FROM Users
WHERE Gender = @Gender;
區域變數會讓 SQL Server 沒辦法精準地去查預估值，導致他只能用平均，然後就給一個錯誤的執行計畫。
我個人在寫 T-SQL 的時候，如果有效能考量都會盡量不寫區域變數，我會把變數放在後端由後端帶進來一個完整的 T-SQL 語法，目的就是為了讓估值變得更準。
這裡有另外一個課題叫做參數嗅嘆，一樣之後再說。
只有在真的不了解某個特定估值從何而來的時候，這才有用
可以用擴充事件的 query_optimizer_estimate_cardinality 來擷取
還有這個是 Debug channel 的一部份，前面介紹擴充事件有講過，這個 channel 要小心謹慎使用，最好是都不要用或是在非正式環境中使用
CREATE EVENT SESSION [CardinalityEstimation]
ON SERVER
ADD EVENT sqlserver.auto_stats
(
WHERE ([sqlserver].[database_name] = N'AdventureWorks2022')
),
ADD EVENT sqlserver.query_optimizer_estimate_cardinality
(
WHERE ([sqlserver].[database_name] = N'AdventureWorks2022')
),
ADD EVENT sqlserver.sql_batch_completed
(
WHERE ([sqlserver].[database_name] = N'AdventureWorks2022')
),
ADD EVENT sqlserver.sql_batch_starting
(
WHERE ([sqlserver].[database_name] = N'AdventureWorks2022')
)
ADD TARGET package0.event_file
(
SET filename = N'cardinalityestimation'
)
WITH
(
TRACK_CAUSALITY = ON
);
SELECT so.Description AS 特價方案描述,
p.Name AS 產品名稱,
p.ListPrice AS 標價,
p.Size AS 尺寸,
pv.AverageLeadTime AS 平均交貨時間,
pv.MaxOrderQty AS 最大訂購數量,
v.Name AS 供應商名稱
FROM Sales.SpecialOffer AS so
JOIN Sales.SpecialOfferProduct AS sop
ON sop.SpecialOfferID = so.SpecialOfferID
JOIN Production.Product AS p
ON p.ProductID = sop.ProductID
JOIN Purchasing.ProductVendor AS pv
ON pv.ProductID = p.ProductID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = pv.BusinessEntityID
WHERE so.DiscountPct > .15;
擴充事件裡面我們會關心的是 input_relation、stats_collected 裡面的 card 值
這顯示資料表整體的 cardinality 的數量。例如這裡的 460 代表 Purchasing.ProductVendor 的筆數。
至於為什麼我不推薦使用擴充事件，因為這些資訊在執行計劃裏面就會有了，而且資訊會更豐富
這個擴充事件的價值是 **Cardinality Estimation Engine version，**當我們在 SQL Server 版本升級，或是由 Cardinality Estimator 引起的問題時，這項資訊會很有用。
所以他才被歸類在 debug chanel 裡面，沒必要的時候不需要去開他。
前面幾乎都是在討論單一欄位
接下來進入多欄位的時候統計資料會怎麼處理
單一索引欄位，統計資料是由該欄位的 Histogram 跟 Density 定義。
但是當索引有複合鍵的時候，資訊就會不一樣了。
Histogram 雖然一樣會用相同的形式建立，但是只有複合 key 中的第一個欄位，是用來建立 histogram 的。
這會使得索引 key 中的欄位順序非常重要，因為只有第一個欄位會取得 histogram。因此，會希望使用資料分佈最佳的欄位作為第一欄。一般來說，也就是選擇性最高的欄位，也就是 Density 最低的欄位。
雖然複合索引只會有一個 histogram，但是他會有多個 density。不過他不是獨立的 density，他是類似這種
欄位1
欄位1 + 欄位2
欄位1 + 欄位2 + 欄位 3
這種的 density
要建立多 density 只有兩種情況
--先看一下 product 的統計資訊
SELECT s.name AS 統計資料名稱,
s.auto_created AS 是否自動建立,
s.user_created AS 是否使用者建立,
s.filter_definition AS 篩選條件定義,
sc.column_id AS 欄位ID,
c.name AS 欄位名稱
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON sc.stats_id = s.stats_id
AND sc.object_id = s.object_id
JOIN sys.columns AS c
ON c.column_id = sc.column_id
AND c.object_id = s.object_id
WHERE s.object_id = OBJECT_ID('Production.Product');
現有的統計資料都沒有包含超過一個欄位。
--然後建立一個有多重KEY 的索引
CREATE NONCLUSTERED INDEX FirstIndex
ON dbo.Test1
(
C1,
C2
)
WITH (DROP_EXISTING = ON);
DBCC SHOW_STATISTICS(Test1, FirstIndex);
就可以看到 Density 有多重key 了，但是 Histogram 沒有任何改變，因為如同前面說的，Histogram 永遠只會建立在第一個欄位上
filtered index 是什麼就不講了，這是基礎的
那因為它的本質，所以支援這種索引的 density 和 histogram 會由不同的資料組成
為了實際觀察這個行為，我在Sales.SalesOrderHeader table 上建立一個索引
--先建立一個一班的 Index
CREATE INDEX IX_Test ON Sales.SalesOrderHeader
(PurchaseOrderNumber);
--然後看他的統計資訊
DBCC SHOW_STATISTICS('Sales.SalesOrderHeader', 'IX_Test');
--再把它改成 filtered index
CREATE INDEX IX_Test
ON Sales.SalesOrderHeader (PurchaseOrderNumber)
WHERE PurchaseOrderNumber IS NOT NULL
WITH (DROP_EXISTING = ON);
--然後再看他的統計資訊
DBCC SHOW_STATISTICS('Sales.SalesOrderHeader', 'IX_Test');
兩個一對比之下，可以看到，rows 數量從 31465 降到 3806
因為不再有 Null 值所以 Average Length 也增加了
兩個值的 Density 很接近，但 filtered density 稍微低一些，代表唯一值比較少。
這是因為被篩選後的資料，雖然選擇性稍微低一些，但實際上更準確，因為它排除了所有不會對資料篩選有幫助的空值。
然後 histogram 會差不多，只少了一筆因為就只有少 null 而已。
要更精細調整 histogram 的話就要手動建 filtered statistics
這點在分割表上特別有用，而且我認為是必要的
因為統計資料不會在分割表上自動建立，而且也無法使用 CREATE STATISTICS 自動建立。
但是我們可以依照分割去建立 FILTERED INDEX，然後取得統計資料
或是專門特別針對分割去建立 FILTERED INDEX
剛剛有說過 2014 以後，系統會自動設定為使用最新的 Cardinality 估算引擎
但因為最新版的估算引擎是預設資料之間是有相關性的，如果不想要這樣預設，可以去調整相容性層級。
相容性層級 120 是門檻，120 以上的話會用新的，110 以下的話會用舊的。
但因為相容性層級是 database 的設定，有時候並不是所有查詢都要用舊的或新的，所以其實去調整這個引擎的選擇有以下幾種方式 :
LEGACY_CARDINALITY_ESTIMATION 資料庫設定FORCE_LEGACY_CARDINALITY_ESTIMATION Query HintFORCE_LEGACY_CARDINALITY_ESTIMATION
選擇哪一種方式，取決於你需要多細緻地調整系統上的 Cardinality Estimation 行為，以及你正在使用的 SQL Server 版本。
最不細緻的選項，是設定資料庫的 Compatibility Level。
--為了接下來的示範，先調成 110
ALTER DATABASE AdventureWorks2022 SET
COMPATIBILITY_LEVEL = 110;
這個 110 會讓 SQL Server 看起來很像 2012，很多比較新的現代化行為也會被停用。
一般來說，不建議這樣開。
下一個 TF 9481，可以用在 2014 以後的版本，但在基礎那邊有說過 TF 這東西不建議去動，在這邊動的話會讓之後很難檢視個別資料庫的設定，因為 TF 只會回傳你一串數字。
比較好的方式是用 DATABASE SCOPED CONFIGURATION 去調，因為這可以很清楚看到目前做了什麼選擇。但這個要 2016 以後的版本才可以用。
ALTER DATABASE SCOPED CONFIGURATION SET
LEGACY_CARDINALITY_ESTIMATION = ON;
也可以用 Query Store Hint
SELECT p.Name,
p.Class
FROM Production.Product AS p
WHERE p.Color = 'Red'
AND p.DaysToManufacture > 15
OPTION (USE HINT
('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
這有分手動跟自動
自動維護的話有四個主要設定
Auto Create Statistics
針對沒有索引的欄位建立新的統計資料
Auto Update Statistics
自動更新既有的統計資料
Sampling rate of statistics
統計資料取樣比例，一般來說不會用這個這是抽樣統計，因為抽樣統計，一般預設是用full
Auto Update Statistics Asynchronously
在查詢執行後更新統計資料
這個自動更新可以在 DATABASE 層級上設定也可以針對個別索引去控制。
只有 Auto Create Statistics 只適用於沒有索引的欄位。
前兩個很簡單已經討論過
當沒有索引的欄位被用在 join、where、having 的時候，統計資料會自動建立
這是預設的，一般來說也應該一直保持啟用
這個也是說過除非經過充分測試，而且正明在某個系統上停用這個功能所帶來的好處值得承擔統計資料過期的風險，否則應該保留預設設定。如果停用這個，那就必須建立手動更新統計資料的維護流程
非同步更新統計資訊
這要牽扯到統計資料是何時知道要更新的?
如果去做 DML 的話會有一個異動計數器去記錄異動筆數
然後等到下一次這個欄位被使用到的時候，他才會去看說這個異動紀錄如果很大已經標記成要更新的時候，這時候才會觸發更新統計資料的機制。
而非同步更新就是在這個 SELECT 觸發更新統計的時候，告訴他這次先不要更新，等我這 SELECT 跑完再更新。
因為當下去更新的話，這次 SELECT 就會變慢，除了要更新統計資訊外，還要做一個新的執行計畫。
當然看懂他的原理之後，你也會去想，難道當下值接用新的統計資訊跑出來的執行計畫不會更好嗎?
確實是有可能會更好的，因為你不去更新統計資訊他就是用舊版，那這時候就要先去測試看看，確保開啟這功能會不會弊大於利?
自動維護其實已經很成熟了，但是有些特殊情況還是可能需要手動維護
統計資料實驗 :
這個就是測試，不要再正式環境做
從舊版升級到新版
如果要升級，應該要在升級流程中善用 Query Store，這東西看到這裡應該還不會但沒關係
只要知道我建議先善用 Query Store 的意思就是，你在升級後不要立刻更新統計資料，應該先讓他自動更新
然後我們再透過 Query Store 升級流程變更相容性模式的時候，那時就有可能會想要手動更新所有統計資訊。
看不懂正常，反正就是舊版升級到新版，正常流程中，會用到手動更新。
執行一次性的 Ad Hoc SQL 活動
在這類情況下，可能需要在自動統計資料維護與手動流程之間做選擇。
手動流程可以讓我們精確控制統計資料何時、以及如何被更新。這通常只會是遠大於一般規模的資料庫才需要關心的問題。
自動更新統計資料觸發的不夠頻繁
這才是最常遇到需要手動更新的情境，如果查詢變慢，然後又發現估計資料數跟實際資料數之間很常有很大差距的時候
就需要手動介入。
--關掉自動建立
ALTER DATABASE AdventureWorks2022 SET AUTO_CREATE_STATISTICS OFF;
--關掉自動更新
ALTER DATABASE AdventureWorks2022 SET AUTO_UPDATE_STATISTICS OFF;
--起用非同步更新統計資料
--要用這個之前要先確保上面那一個自動更新有打開
ALTER DATABASE AdventureWorks20220 SET
AUTO_UPDATE_STATISTICS_ASYNC ON;
--也可以不用設定整個資料庫
--這是關閉 Department 這張表的自動更新
USE AdventureWorks2022;
EXEC sp_autostats
'HumanResources.Department',
'OFF';
--甚至更細到停用 Department 這張 table 上面的 AK_Department_Name index 的自動更新
EXEC sp_autostats
'HumanResources.Department',
'OFF',
AK_Department_Name;
--檢查目前自動更新統計資料狀態
EXEC sp_autostats 'HumanResources.Department';
有兩種方法
CREATE STATISTICS
這個可以在 table 或是 indexed view 上的單一欄位或多欄位建立統計資料。
但有別於一般 CREATE INDEX，CREATE STATISTICS 預設是使用 SAMPLE 的方式去建立統計資料。
sys.sp_createstats
這是用來更新目前資料庫中所有使用者資料表的統計資料。
但是，這個永遠都只能用 sample 的方式去建立。這是一種很粗略的統計資料維護工具。
在某些形況下，只依靠自動更新統計資料並不足夠
所以在離峰時間排程執行 update statistics，是一種建議方式。
sample 方式比較不準確，雖然他比較快。
如果要準確反映資料的狀況，可以強制使用 fullscan，讓所有資料都被用來更新統計資料不是靠抽樣。
但是 fullscan 是成本很高的操作，所以最好有選擇性的決定那些統計資料需要處理。
但是手動建立會有一個問題 :
會被視為永久物件，所以他會去阻止 table 變更
例如說 在某一個欄位上建立統計資料，然後我要刪除這個欄位的時候
CREATE STATISTICS ST_Users_Gender
ON dbo.Users(Gender);
ALTER TABLE dbo.Users
DROP COLUMN Gender;
會噴錯，因為統計資料已經被視為永久物件了，他要有相依的欄位才可以存活
要解決這個問題的話，自 2022 以後，有一個自動刪除統計資料的方法，就是在建立統計資料的時候加入 WITH AUTO_DROP = ON
前面都是在說統計資料的原理、維護、建立。
接下來要真正在執行計畫上看這些統計資訊對效能的影響。
為了協助最佳化器建立盡可能最佳的執行計畫，必須維護資料庫物件上的統計資料。
統計資料過舊、不正確、遺失或不準確，都是相當常見的問題。
在分析查詢效能時，注意是否可能存在統計資料方面的問題。
事實上，在查詢調校作業一開始，就先確認統計資料是否為最新狀態，可以排除一個容易修正的問題。
這裡的重點會放在執行計畫上
通常需要檢查以下幾點 :
為了示範統計資料遺失會發生什麼事，等等首先我會停用自動建立統計資料跟停用自動更新
然後建立測試 table、index跟 data
--停用自動建立
ALTER DATABASE AdventureWorks2022 SET
AUTO_CREATE_STATISTICS OFF;
--停用自動更新
ALTER DATABASE AdventureWorks2022 SET
AUTO_UPDATE_STATISTICS OFF;
GO
DROP TABLE IF EXISTS dbo.Test1;
GO
--建新測試表
CREATE TABLE dbo.Test1
(
C1 INT,
C2 INT,
C3 CHAR(50)
);
INSERT INTO dbo.Test1
(
C1,
C2,
C3
)
VALUES
(51, 1, 'C3'),
(52, 1, 'C3');
--建立索引，復合 key
--這時候會有統計資訊，雖然前面停用，但是建立索引就一定會有，前面停用是針對
--where 或是 join 遇到沒有統計資訊的時候她去自動建立
--這時候的統計資訊不用去看也知道會包含
--c1 的 histogram
--c1 的 density
--c1,c2 的 density
CREATE NONCLUSTERED INDEX iFirstIndex ON dbo.Test1
(C1, C2);
--亂塞資料
SELECT TOP 10000
IDENTITY(INT, 1, 1) AS n
INTO #Nums
FROM master.dbo.syscolumns AS sc1,
master.dbo.syscolumns AS sc2;
INSERT INTO dbo.Test1
(
C1,
C2,
C3
)
SELECT n % 50,
n,
'C3'
FROM #Nums;
DROP TABLE #Nums;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--然後隨便查一下，去看她的執行計畫
SELECT t.C1,
t.C2,
t.C3
FROM dbo.Test1 AS t
WHERE t.C2 = 1;
可以看到這個查詢經過 5豪秒、102次讀取
兩件事
--為了解決這個問題，我們就手動建 C2 的統計資訊
--是因為我們把它關掉了，不然她原本會自動建
CREATE STATISTICS Stats1 ON Test1(C2);
--保險一點可以先把剛剛做的執行計劃刪掉 不刪也沒關係只是要確保她不會繼續用舊的而已
DECLARE @Planhandle VARBINARY(64);
SELECT @Planhandle = deqs.plan_handle
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text = 'SELECT *
FROM dbo.Test1
WHERE C2 = 1;';
IF @Planhandle IS NOT NULL
BEGIN
DBCC FREEPROCCACHE(@Planhandle);
END;
--然後再執行一次
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--然後隨便查一下，去看她的執行計畫
SELECT t.C1,
t.C2,
t.C3
FROM dbo.Test1 AS t
WHERE t.C2 = 1;
可以看到執行計畫整個變了
因為有了統計資訊，最佳化器可以精準預估 3 筆，所以她由此判斷那最好的做法是掃描 iFirstIndex。
然後因為 index 只包含部分資料，所以她必須回到 table 本身去做 Lookup 的動作。
從截圖的IO 跟 TIME 資訊可以看出原本讀取是 102 現在降低到 51 這是好事。但是經過時間從 5ms 上升到 12ms 這是壞事
這時候就要去判斷原因，可以靠 AI、靠經驗之類的，很難把所有狀況都一次列出來，就算一次列出來也不可能全部背下來
這邊會造成經過時間上升的原因是因為，Lookup。
因為索引裏面沒有 include C3 這個欄位，所以造成雖然讀取變少，但是花費時間變多。
印象中以前有寫過整個掃描 TABLE，不一定會筆掃描 INDEX 還要慢，就在這裡出現實例了。
於是我又去把 INDEX 加上 INCLUDE C3 ，然後手動 UPDATE C2 的 STATISTICS
DROP INDEX iFirstIndex ON dbo.Test1
(C1, C2);
CREATE NONCLUSTERED INDEX iFirstIndex ON dbo.Test1
(C1, C2) INCLUDE (C3);
UPDATE STATISTICS dbo.Test1 Stats1 WITH FULLSCAN;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--然後再看一次
SELECT t.C1,
t.C2,
t.C3
FROM dbo.Test1 AS t
WHERE t.C2 = 1;
執行計劃就會只剩下索引掃描，沒有 Lookup 了，時間也從 12ms 降到 1ms，雖然讀取次數比沒有加 include之前增加，但相比於最一開始沒有統計資訊來講還是一樣是有減少的，而且總時間也減少，那這就達成調校的目的了。
過期或不正常的統計資料，那其實跟沒有統計資料是一樣的意思
但重點在於，如果是因為過期，那執行計畫不會像缺少統計資料的時候一樣給一個明顯的警示。
她會繼續用過期的統計資訊，會導致看不出來。
所以只能透過去看估計與實際資料之間的比較去判斷是不是有過期的狀況發生。
有一個 debug 擴充事件，可以在統計資料嚴重偏差的時候顯示一些事件 :
inaccurate_cardinality_estimate
但是因為他是 debug 事件，所以一樣，建議非常謹慎使用，而且只在有強力篩選條件的情況下，並且只短時間使用。
--先看一下目前統計資訊
DBCC SHOW_STATISTICS (Test1, iFirstIndex);
-- 然後換一個查詢，這次去查 C1 = 51
SELECT C1,
C2,
C3
FROM dbo.Test1
WHERE C1 = 51;
可以看到實際只回傳 1 筆，但是因為統計資料沒有更新，最佳話器預估會有 5001 筆資料
--所以去手動更新統計資料
UPDATE STATISTICS Test1 iFirstIndex
WITH FULLSCAN;
--這邊用 FULLSCAN 通常不是必要，但我建議在離峰時段使用 FULLSCAN，這會讓統計更準
-- 然後再用一樣的查詢去看ㄎ
SELECT C1,
C2,
C3
FROM dbo.Test1
WHERE C1 = 51;
可以看到這次讀取從102 降低到 4
於時間寫 0ms 是因為SET STATISTICS TIME ON; 這個只會擷取到毫秒
如果用擴充事件擷取的話可以到微秒
--實驗完 再把這些打開
ALTER DATABASE AdventureWorks2022 SET
AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2022 SET
AUTO_UPDATE_STATISTICS ON;
建議讓這件事自然發生，而不是在升級後立刻嘗試更新所有統計資料。
這項功能應該保持開啟。
如此一來，SQL Server 就可以在沒有索引的欄位上，建立它所需要的統計資料，進而產生更好的執行計畫，通常也會帶來更好的效能。
在某些情況下，可能會發現自行建立複合統計資料是有幫助的。
這項功能也應該保持開啟，讓 SQL Server 可以隨著資料分佈隨時間改變，取得更準確的執行計畫。
通常來說，效能收益會大於維護成本。
如果確實遇到 Auto Update Statistics 功能造成的問題，並決定停用它，那麼你必須確保自己建立一套自動化流程，定期更新統計資料。
基於效能考量，在可行的情況下，應該將這個流程安排在離峰時間執行。
你很可能仍然需要補充自動統計資料維護。
至於需要針對整個資料庫執行，還是只針對特定 index 或 statistics 執行，則取決於你的系統行為。
在大多數情況下，等待 statistics 更新完成後再產生執行計畫，是可以接受的。
但在某些情況下，如果 statistics 更新本身，或因為 statistics 更新而導致的 execution plan 重新編譯，比使用過期 statistics 所造成的成本還要昂貴，那麼就可以啟用這項功能。
只是要理解，這可能代表某些原本可以受益於較新 statistics 的查詢，會在下一次執行之前受到影響。
不要忘記，若要啟用 asynchronous updates，必須先啟用 automatic update of statistics。
一般建議使用預設的取樣比例。
這個比例是由一套演算法決定，會根據資料大小與修改次數來判斷。
雖然預設取樣比例在大多數情況下是最佳選擇，但如果你針對某個特定查詢發現 statistics 不夠準確，就可以手動使用 FULLSCAN 更新它們。
也可以使用 SAMPLE 數值來設定特定取樣比例。這個數值可以是百分比，也可以是固定的資料列數。
如果這件事需要重複執行，可以加入一個 SQL Server job 來處理。
基於效能考量，請確保這個 SQL job 被安排在離峰時間執行。
若要找出哪些情況下預設取樣比例不夠好，可以在排查資料庫效能問題時，分析高成本查詢的 statistics effectiveness。
FULLSCAN 成本很高，所以只應該在你已經判斷確實能受益的資料表或 index 上執行
iThome鐵人賽
看影片追技術